package database;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import objectView.EmSalarySheet;
import objectView.EmTimeSheet;
import objectView.EmployeeDepCom;
import objectView.EmployeeInfo;
import utility.StringUtil;
import clientMain.Login;
import clientMain.Main;

public class ViewEmDelegate{
	public EmployeeInfo getEmployee(String emId){
		EmployeeInfo em  = new EmployeeInfo();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_EmployeeInfo(?)}");
			cst.setString(1, emId);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				
				em.setEmId(emId);
				em.setEmFullName(StringUtil.getFilterIllegal(rs.getString("Em_FullName")));
				em.setEmBirthDay(StringUtil.getFilterIllegal(rs.getString("Em_BirthDay")));
				em.setSex(rs.getBoolean("Em_Sex"));
				em.setEmCMTND(StringUtil.getFilterIllegal(rs.getString("Em_CMTND")));
				em.setEmHT(StringUtil.getFilterIllegal(rs.getString("Em_HomeTownAdd")), 
						StringUtil.getFilterIllegal(rs.getString("District_HT")), 
						StringUtil.getFilterIllegal(rs.getString("Province_HT")));
				em.setEmCur(StringUtil.getFilterIllegal(rs.getString("Em_CurrentAdd")), 
						StringUtil.getFilterIllegal(rs.getString("District_Cur")), 
						StringUtil.getFilterIllegal(rs.getString("Province_Cur")));
				em.setEmMobile(StringUtil.getFilterIllegal(rs.getString("Em_Mobile")));
				em.setEmPhone(StringUtil.getFilterIllegal(rs.getString("Em_Phone")));
				em.setEmEmail(StringUtil.getFilterIllegal(rs.getString("Em_Email")));
				em.setEmImage(StringUtil.getFilterIllegal(rs.getString("Em_Image")));
				em.setDiplomasName(rs.getString("Diplomas_Name"));
				em.setSpecName(StringUtil.getFilterIllegal(rs.getString("Specialized_Name")));
				em.setDepId(rs.getInt("Dep_Id"));
				em.setDepName(StringUtil.getFilterIllegal(rs.getString("Dep_Name")));
				em.setPosName(StringUtil.getFilterIllegal(rs.getString("Pos_Name")));
				em.setEmTName(StringUtil.getFilterIllegal(rs.getString("EmT_Name")));
				em.setContractName(StringUtil.getFilterIllegal(rs.getString("Contract_Name")));
				em.setEmDateStart(StringUtil.getFilterIllegal(rs.getString("DateStart")));
				em.setEmDateEnd(StringUtil.getFilterIllegal(rs.getString("DateEnd")));
				
			}
			Login.conn.commit();
			return em;
		} catch (SQLException e) {
			e.printStackTrace();
			return em = null;
		}		
	}

	public Vector<EmployeeInfo> getEmployee1(String emId){
		Vector<EmployeeInfo> vt = new Vector<EmployeeInfo>();
		EmployeeInfo em = new EmployeeInfo();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_EmployeeInfo(?)}");
			cst.setString(1, emId);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){	
				em.setEmId(emId);
				em.setEmFullName(StringUtil.getFilterIllegal(rs.getString("Em_FullName")));
				em.setDepName(StringUtil.getFilterIllegal(rs.getString("Dep_Name")));
				em.setPosName(StringUtil.getFilterIllegal(rs.getString("Pos_Name")));
				em.setSex(rs.getBoolean("Em_Sex"));
				em.setEmBirthDay(StringUtil.getFilterIllegal(rs.getString("Em_BirthDay")));
				em.setEmPhone(StringUtil.getFilterIllegal(rs.getString("Em_Phone")));
				em.setEmCur(StringUtil.getFilterIllegal(rs.getString("Em" +
						"_CurrentAdd")), 
						StringUtil.getFilterIllegal(rs.getString("District_Cur")), 
						StringUtil.getFilterIllegal(rs.getString("Province_Cur")));
				em.setSpecName(StringUtil.getFilterIllegal(rs.getString("Specialized_Name")));
				em.setDiplomasName(rs.getString("Diplomas_Name"));
				em.setEmDateStart(StringUtil.getFilterIllegal(rs.getString("DateStart")));
				em.setEmDateEnd(StringUtil.getFilterIllegal(rs.getString("DateEnd")));
				vt.add(em);				
			}
			Login.conn.commit();
			return vt;
		} catch (SQLException e) {
			e.printStackTrace();
			return vt = null;
		}		
	}
	
	
	/**
	 * @author Tidus
	 * @param page int
	 * @param check: [true]: xem tat ca nhan vien co DateEnd IS NULL || DateEnd > DateNow, 
	 * 				 [false] : xem toan bo danh sach NV tu trc den nay
	 * @param sort [Em_Id],[Em_LastName], [Dep_Name], [Diplomas_Name], [Specialized_Name]
	 * @param flag [true]: be->lon, [false]: lon->be
	 * @return list Employee In Company
	 */
	public Vector<EmployeeDepCom> getListEmInCom(boolean check, int page, String sort, boolean flag){
		Vector<EmployeeDepCom> list = new Vector<EmployeeDepCom>();
		try{
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListEmInCom(?,?,?,?,?,?)}");
			cst.setInt(1, Main.comId);
			cst.setInt(2, Main.record);
			cst.setBoolean(3, check);
			cst.setInt(4, page);
			cst.setString(5, sort);
			cst.setBoolean(6, flag);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				EmployeeDepCom em = new EmployeeDepCom();
				em.setEmId(rs.getString("Em_Id"));
				em.setEmFullName(StringUtil.getFilterIllegal(rs.getString("Em_FullName")));
				em.setEmMobile(StringUtil.getFilterIllegal(rs.getString("Em_Mobile")));
				em.setDepName(StringUtil.getFilterIllegal(rs.getString("Dep_Name")));
				em.setPosName(StringUtil.getFilterIllegal(rs.getString("Pos_Name")));
				em.setEmTName(StringUtil.getFilterIllegal(rs.getString("EmT_Name")));
				em.setContractName(StringUtil.getFilterIllegal(rs.getString("Contract_Name")));
				em.setEmDateStart(StringUtil.getFilterIllegal(rs.getString("DateStart")));
				em.setEmDateEnd(StringUtil.getFilterIllegal(rs.getString("DateEnd")));
				list.add(em);
			}
			Login.conn.commit();
 		}catch (Exception e) {
             e.printStackTrace();
             list = null;
 		}
 		return list;
	}

	
	/**
	 * @author Tidus
	 * @param depId
	 * @param check: [true]: xem tat ca nhan vien co DateEnd IS NULL || DateEnd > DateNow, 
	 * 				[false] : xem toan bo danh sach NV tu trc den nay
	 * @param option: [true]: xem Em_FullName, Em_Id, Em_Position hien tai(dung trong timesheet)
	 * @param page
	 * @param sort: [Em_Id], [Em_LastName], [Pos_Name], [Em_DateStart]
	 * @param flag [true]: be->lon, [false]: lon->be
	 * @return list EmployeeDepCom
	 */
	public Vector<EmployeeDepCom> getListEmInDep(int depId, boolean check, boolean option, 
											int page, String sort, boolean flag){
		Vector<EmployeeDepCom> list = new Vector<EmployeeDepCom>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListEmInDep(?,?,?,?,?,?,?)}");
			cst.setInt(1, depId);
			cst.setBoolean(2, check);
			cst.setBoolean(3, option);
			cst.setInt(4, page);
			cst.setInt(5, Main.record);
			cst.setString(6, sort);
			cst.setBoolean(7, flag);
			ResultSet rs = cst.executeQuery();
			while (rs.next()) {
				EmployeeDepCom em = new EmployeeDepCom();
				em.setEmId(rs.getString("Em_Id"));
				em.setEmFullName(StringUtil.getFilterIllegal(rs.getString("Em_FullName")));
				if(page != 0){
					em.setPosId(rs.getInt("Em_Pos"));
					em.setPosName(StringUtil.getFilterIllegal(rs.getString("Pos_Name")));
					if(option == false){
						em.setEmMobile(StringUtil.getFilterIllegal(rs.getString("Em_Mobile")));	
						em.setEmTName(StringUtil.getFilterIllegal(rs.getString("EmT_Name")));
						em.setContractName(StringUtil.getFilterIllegal(rs.getString("Contract_Name")));
						em.setEmDateStart(StringUtil.getFilterIllegal(rs.getString("DateStart")));
						em.setEmDateEnd(StringUtil.getFilterIllegal(rs.getString("DateEnd")));
					}
				}
				list.add(em);
			}
			Login.conn.commit();
		} catch(Exception e) {
			e.printStackTrace();
			list = null;
		}
        return list;
	 }
	
	
	/**
	 * @author Tidus
	 * @param monthYear String[MM/YYYY]
	 * @param depId int
	 * @param page int
	 * @param sort [Em_Id], [Em_LastName], [totalSalary]
	 * @param flag [true]: be->lon, [false]: lon->be
	 * @return list EmSalarySheet
	 */
	public Vector<EmSalarySheet> getListEmSS(String monthYear,int depId, int page, String sort, boolean flag){
		Vector<EmSalarySheet> list = new Vector<EmSalarySheet>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_ListEmSS(?,?,?,?,?,?)}");
			cst.setString(1, monthYear);
			cst.setInt(2, depId);
			cst.setInt(3, page);
			cst.setInt(4, Main.record);
			cst.setString(5, sort);			
			cst.setBoolean(6, flag);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				EmSalarySheet emSS = new EmSalarySheet();
				emSS.setEmId(rs.getString("Em_Id"));
				emSS.setEmFullName(StringUtil.getFilterIllegal(rs.getString("Em_FullName")));
				emSS.setScAM(StringUtil.getFilterIllegal(rs.getString("SC_Name_AM")), 
						rs.getBoolean("SC_AM"), rs.getInt("SC_Money_AM"));
				emSS.setTotalAM(Float.toString(rs.getFloat("TotalAM")));
				emSS.setSalaryAM(Integer.toString(rs.getInt("SalaryAM")));
				emSS.setScPM(StringUtil.getFilterIllegal(rs.getString("SC_Name_PM")),
						rs.getBoolean("SC_PM"), rs.getInt("SC_Money_PM"));
				emSS.setTotalPM(Integer.toString(rs.getInt("TotalPM")));				
				emSS.setSalaryPM(Integer.toString(rs.getInt("SalaryPM")));
				emSS.setScYear(StringUtil.getFilterIllegal(rs.getString("SC_Name_Year")), 
						rs.getInt("SC_Money_Year"));
				emSS.setSalaryYear(Integer.toString(rs.getInt("SalaryYear")));
				emSS.setSb(StringUtil.getFilterIllegal(rs.getString("SB_Name")), 
						rs.getInt("SB_Money"));
				emSS.setContractBenefit(Integer.toString(rs.getInt("Contract_Benefit")));
				emSS.setTotalSalary(Integer.toString(rs.getInt("TotalSalary")));
				list.add(emSS);
			}
			Login.conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			list = null;
		}
        return list;
	}
	
	/**
	 * @author Tidus
	 * @param monthYear String[MM/YYYY]
	 * @param emId String
	 * @return Employee SalarySheet
	 */
	public EmSalarySheet getEmSalarySheet(String monthYear, String emId){
		EmSalarySheet emSS = new EmSalarySheet();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{ call sp_EmployeeSS(?,?) }");
			cst.setString(1, monthYear);
			cst.setString(2, emId);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				emSS.setEmId(rs.getString("Em_Id"));
				emSS.setEmFullName(StringUtil.getFilterIllegal(rs.getString("Em_FullName")));
				if(!monthYear.contains("13")){
					emSS.setScAM(StringUtil.getFilterIllegal(rs.getString("SC_Name_AM")), 
							rs.getBoolean("SC_AM"), rs.getInt("SC_Money_AM"));
					emSS.setTotalAM(Float.toString(rs.getFloat("TotalAM")));
					emSS.setSalaryAM(Integer.toString(rs.getInt("SalaryAM")));
					emSS.setScPM(StringUtil.getFilterIllegal(rs.getString("SC_Name_PM")), 
							rs.getBoolean("SC_PM"), rs.getInt("SC_Money_PM"));
					emSS.setTotalPM(Integer.toString(rs.getInt("TotalPM")));				
					emSS.setSalaryPM(Integer.toString(rs.getInt("SalaryPM")));
				}else{
					emSS.setScYear(StringUtil.getFilterIllegal(rs.getString("SC_Name_Year")), 
							rs.getInt("SC_Money_Year"));
					emSS.setSalaryYear(Integer.toString(rs.getInt("SalaryYear")));
				}
				emSS.setSb(StringUtil.getFilterIllegal(rs.getString("SB_Name")), 
						rs.getInt("SB_Money"));
				emSS.setContractBenefit(Integer.toString(rs.getInt("Contract_Benefit")));
				emSS.setTotalSalary(Integer.toString(rs.getInt("TotalSalary")));
			}
			Login.conn.commit();
			return emSS;
		} catch (SQLException e) {
			e.printStackTrace();
			return emSS = null;
		}
	}
	
	
	/**
	 * @author Tidus
	 * @param monthYear String[MM/YYYY]
	 * @param emId [NV-xxxx]
	 * @return list EmTimeSheet
	 */
	public Vector<EmTimeSheet> getEmTimeSheet(String monthYear, String emId){
		Vector<EmTimeSheet> list = new Vector<EmTimeSheet>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_EmployeeTS(?,?)}");
			cst.setString(1, monthYear);
			cst.setString(2, emId);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				EmTimeSheet emTS = new EmTimeSheet();
				emTS.setTsId(rs.getInt("TS_Id"));
				emTS.setTsDate(rs.getString("Date"));
				emTS.setEmId(rs.getString("Em_Id"));
				emTS.setEmFullName(StringUtil.getFilterIllegal(rs.getString("Em_FullName")));
				emTS.setTsMorning(rs.getBoolean("TS_Morning"));
				emTS.setTsAfternoon(rs.getBoolean("TS_Afternoon"));
				emTS.setTsEvening(rs.getBoolean("TS_Evening"));
				list.add(emTS);
			}
			Login.conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			list = null;
		}
        return list;
	}
	
	public Vector<EmployeeInfo> searchEm(int page, String column, String search){
		Vector<EmployeeInfo> list = new Vector<EmployeeInfo>();
		try {
			Login.conn.setAutoCommit(false);
			CallableStatement cst = Login.conn.prepareCall("{call sp_SearchEm(?,?,?,?,?)}");
			cst.setInt(1, Main.comId);
			cst.setInt(2, Main.record);
			cst.setInt(3, page);
			cst.setString(4, column);
			cst.setString(5, search);
			ResultSet rs = cst.executeQuery();
			while(rs.next()){
				EmployeeInfo em = new EmployeeInfo();
				em.setEmId(rs.getString("Em_Id"));
				em.setEmFullName(StringUtil.getFilterIllegal(rs.getString("Em_FullName")));
				em.setEmMobile(StringUtil.getFilterIllegal(rs.getString("Em_Mobile")));
				em.setEmEmail(StringUtil.getFilterIllegal(rs.getString("Em_Email")));
				em.setDepId(rs.getInt("Dep_Id"));
				em.setDepName(StringUtil.getFilterIllegal(rs.getString("Dep_Name")));
				em.setPosName(StringUtil.getFilterIllegal(rs.getString("Pos_Name")));
				em.setEmContract(rs.getInt("Contract_Id"));
				list.add(em);
			}
			Login.conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			list = null;
		}
		return list;
	}
		
}
